package com.zengqingfa.imp.demo.service;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.*;

/**
 * @fileName: MergeFile
 * @author: zengqf3
 * @date: 2021-3-3 17:17
 * @description:
 */
@Service
public class MergeFile {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        long startTime = System.currentTimeMillis();    //获取开始时间
        String fromPath = "D:\\temp";// excel存放路径
        String toPath = "D:\\tmp";// 保存新EXCEL路径
        // 新的excel 文件名
        String excelName = "汇总";
        // 创建新的excel
        XSSFWorkbook wbCreat = new XSSFWorkbook();
        File file = new File(fromPath);
        for (File excel : file.listFiles()) {
            // 打开已有的excel
            String strExcelPath = fromPath + "/" + excel.getName();
            // input 来源  ，output 目的地
            InputStream in = new FileInputStream(strExcelPath);
            XSSFWorkbook  wb = new XSSFWorkbook(in);
            //获取工作簿的名称，不带前缀
            String bookName = excel.getName().substring(0, excel.getName().lastIndexOf("."));
            // i表示源工作簿 sheet 数量
            int sheetnum = wb.getNumberOfSheets();
            for (int i = 0; i < sheetnum; i++) {
                //开始每个表的处理
                XSSFSheet sheet = wb.getSheetAt(i);
                // 目标工作簿创建同样的sheet名
                String sheetName = sheet.getSheetName();

                int firstRow = sheet.getFirstRowNum();
                int lastRow = sheet.getLastRowNum();

                System.out.println("workbook:" + bookName);
                System.out.println("sheetName:" + sheetName);
                //如果sheet的lastrow 是0 表示这个表没有内容,这张sheet就不用处理，继续处理下张sheet处理，用continue
                if (lastRow == 0) {
                    continue;
                }
                XSSFSheet sheetCreat = wbCreat.createSheet(bookName + "_" + sheet.getSheetName());
                //开始多行的处理
                for (int j = firstRow; j <= lastRow; j++) {
                    // 创建新建excel Sheet的行
                    XSSFRow rowCreat = sheetCreat.createRow(j);
                    // 取得源有excel Sheet的行
                    XSSFRow row = sheet.getRow(j);
                    // 单元格式样
                    //如果行的内容为null ，说明行为空行，此行就跳过，继续下行的处理，用continue
                    if (null == row) {
                        continue;
                    }
                    System.out.println("bookName:" + bookName + "|sheetName:" + sheetName + "|row:" + j);
                    int lastCell = row.getLastCellNum();
                    int firstCell = row.getFirstCellNum();
                    for (int k = firstCell; k < lastCell && lastCell > 0; k++) {
                        System.out.println(row.getCell(k));
                        rowCreat.createCell(k);
                        String strVal = "";
                        if (row.getCell(k) == null) {

                        } else {
                            strVal = row.getCell(k) + "";
                        }
                        rowCreat.getCell(k).setCellValue(strVal);
                    }
                    System.out.println("单行处理完毕");
                }
                System.out.println("多行处理完毕");
            }
            System.out.println("多表处理完毕");
        }
        System.out.println("多个文件处理完毕");
        System.out.println(toPath + excelName + ".xls");
        FileOutputStream fileOut = new FileOutputStream(toPath + excelName + ".xls");

        wbCreat.write(fileOut);
        fileOut.close();

        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(toPath + excelName + ".xls"));
        System.out.println(workbook.getNumberOfSheets());

        long endTime = System.currentTimeMillis();    //获取结束时间

        System.out.println("程序运行时间：" + (endTime - startTime) + "ms");    //输出程序运行时间
    }


}